﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_timecard')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_timecard

GO

-- =============================================
-- Author:      
-- Create date: 2020-06-22 15:05:48
-- Database:    [scm_main-hysj2]
-- Description: 考勤管理-考勤卡计算
-- =============================================

CREATE PROCEDURE bd_proc_timecard 
  @date DATE
 ,@users VARCHAR(MAX) = ''
 ,@flag INT = 0   --0:自动计算  1:补出勤  2:请假 3:加班 4:排班, 5:加班审批（通过、驳回） 6:请假审批（通过、驳回）  7：取消假期
AS
BEGIN
  SET NOCOUNT ON;


  CREATE TABLE #timecard
  (
    user_serial BIGINT 
    , user_no VARCHAR(20) 
    , user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , dept_id BIGINT 
    , dept_name VARCHAR(500) COLLATE Chinese_PRC_CI_AS
    , card_date DATE  --考勤日期
    , card_no INT DEFAULT 1   --一人一天两个班次时会有两条数据，按顺序号排列
    , card_week VARCHAR(20) COLLATE Chinese_PRC_CI_AS
    , work_calendar INT DEFAULT 0 --工作日历
    , work_status INT DEFAULT 0   --0:工作日 1：双休  2：节假
    , lactational_begin DATE      --授乳假有效期开始日期
    , lactational_end DATE        --授乳假有效期结束日期
    , work_age  INT DEFAULT 0

    , card_date_tomorrow  DATE  --考勤日期后一天
    , work_status_tomorrow INT DEFAULT 0   --0:工作日 1：双休  2：节假


    , shift_id INT NOT NULL DEFAULT 0
    , shift_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , shift_begin INT NOT NULL DEFAULT 9999  --上班时间(排班开始时间)
    , shift_end INT NOT NULL DEFAULT 9999    --下班时间(排班结束时间)
    , shift_sign_begin INT DEFAULT 0   --开始签到时间
    , shift_sign_end INT DEFAULT 0     --结束签退时间
    , shift_late INT DEFAULT 0       --迟到结束时间
    , shift_early INT DEFAULT 0      --早退开始时间
    , shift_duty_on_delay INT DEFAULT 0   --上班后X分钟内不计迟到
    , shift_duty_off_delay INT DEFAULT 0   --下班后X分钟内不计早退
    , shift_workday DECIMAL(8, 2) DEFAULT 0         --本时段应记为X工作日
    , shift_workday_hours INT DEFAULT 0   --等于X分钟
    , shift_ot_begin  INT DEFAULT 0   --8+N，N+8上班开始时间
    , shift_ot_end  INT DEFAULT 0   --8+N，N+8下班结束时间

    , shift_is_flextime INT DEFAULT 0       --是否弹性班次
    , shift_flextime_interval_begin INT DEFAULT 0   -- 上班时间弹性间隔
    , shift_flextime_interval_end INT DEFAULT 0   -- 下班时间弹性间隔

    , shift_actual_begin INT DEFAULT 0   -- 实际上班时间
    , shift_actual_end INT DEFAULT 0   -- 实际下班时间

    , shift_begin_swipe_end  DATETIME        --上班打卡结束日期时间，正常排班取shift_late_time_begin，加班申请取加班上班时间
    , shift_end_swipe_begin   DATETIME       --下班打卡开始日期时间，正常排班取shift_early_time_end，加班申请取加班下班时间

    , shift_late_time_begin DATETIME  --迟到开始日期时间，包括不计迟到的分钟数
    , shift_late_time_end DATETIME    --迟到结束日期时间

    , shift_early_time_begin DATETIME  --早退开始日期时间
    , shift_early_time_end DATETIME    --早退结束日期时间，包括不计早退的分钟数

--    , shift_duty_begin DATETIME  --上班打卡日期时间（包括不计迟到的分钟数）,在这之前的打卡算正常
--    , shift_duty_end DATETIME    --下班打卡日期时间（包括不计早退的分钟数）,在这之后的打卡算正常

    , shift_card_begin DATETIME   --打卡签到开始日期时间
    , shift_card_end DATETIME     --打卡签退日期时间

    , shift_allowance_zb1 DECIMAL(4,1) DEFAULT 0          --中班津贴1
    , shift_allowance_zb2  DECIMAL(4,1) DEFAULT 0         --中班津贴2
    , shift_allowance_yb DECIMAL(4,1) DEFAULT 0           --夜班津贴

--    , shift_allowance_id INT         --班次津贴ID
--    , shift_allowance_name  VARCHAR(50)   --班次津贴名称
--    , shift_allowance_hours INT   --大于X小时后计一次班次津贴

    , swipe_begin_actual INT DEFAULT 0   -- 实际上班时间
    , swipe_end_actual INT DEFAULT 0     -- 实际下班时间

    , swipe_begin INT DEFAULT 9999    -- 打卡开始时间
    , swipe_end INT DEFAULT 9999      -- 打卡结束时间

    , swipe_late  INT DEFAULT 0     --迟到打卡时间

    , swipe_early INT DEFAULT 0     --早退打卡时间

    , swipe_time_begin  DATETIME    -- 打卡开始日期时间
    , swipe_time_end  DATETIME      -- 打卡结束日期时间

    , swipe_time_late DATETIME      --迟到打卡日期时间
    , swipe_time_early  DATETIME    --早退打卡日期时间

    , swipe_first INT DEFAULT 9999 -- 第一次打卡时间
    , swipe_last INT DEFAULT 9999   -- 最后一次打卡时间

    , should_hours INT DEFAULT 0   -- 应出工时
    , should_hours_calc INT DEFAULT 0   --计算出勤天数，不足8小时的计算8小时

    , actual_hours INT DEFAULT 0   -- 正常工时
    , actual_hours_calc INT DEFAULT 0       --计算出勤天数，有的班次是7.5小时，按一天8小时计算天数的时候，会有小数，如果出勤正常，7.5小时计为8小时，统计时使用

    , total_hours INT DEFAULT 0    --总工时=正常工时+加班工时+请假时长

    , status INT NOT NULL DEFAULT -1

--    , shift_middle_count INT NOT NULL DEFAULT 0   --中班次数
--    , shift_night_count INT NOT NULL DEFAULT 0    --夜班次数

--    , is_enjoyment_subsidized INT DEFAULT 0       --是否中夜班
--    , middle_shift_start_time INT DEFAULT 0       --中班开始时间
--    , middle_shift_end_time INT DEFAULT 0         --中班结束时间
--    , night_shift_start_time INT DEFAULT 0        --夜班开始时间
--    , night_shift_end_time INT DEFAULT 0          --夜班结束时间

    , repast_start_time INT DEFAULT 0     --就餐1开始时间
    , repast_end_time INT DEFAULT 0       --就餐1结束时间
    , repast_first INT DEFAULT 0         --是否就餐第一次
--    , repast_is_83_normal INT DEFAULT 0   --是否8+3的正常上班就餐，如果就餐时间在加班的时间内，=1

    , repast_start_time2 INT DEFAULT 0    --就餐2开始时间
    , repast_end_time2 INT DEFAULT 0      --就餐2结束时间
    , repast_second INT DEFAULT 0         --是否就餐第二次
--    , repast_is_83_normal2 INT DEFAULT 0  --是否8+3的正常上班就餐，如果就餐时间在加班的时间内，=1

    , repast_hours INT DEFAULT 0   --需要扣除的就餐时长，根据上下班打卡时间，判断是否包含就餐时间，计算就餐几次
--    , repast_count INT DEFAULT 0   -- 就餐次数

    , pending_hours INT NOT NULL DEFAULT 0    -- 待处理时间，小于2小时不显示

    , is_ot INT DEFAULT 0     --是否加班
    , ot_bill_id INT DEFAULT 0         --加班单ID
    , ot_bill_check INT DEFAULT 0       --加班状审批状态
    , ot_bill_status INT DEFAULT 0     --加班单状态 0：不能操作  1：能操作
    , ot_shift_id INT DEFAULT 0         --加班班次ID
    , ot_type_id INT NULL DEFAULT 0     
    , ot_type_83 INT DEFAULT 0          --8+3班次类型，0：8+3   1：3+8
    , ot_type_name VARCHAR(50) COLLATE Chinese_PRC_CI_AS
    , ot_begin INT NOT NULL DEFAULT 0   -- 加班申请开始时间
    , ot_end INT NOT NULL DEFAULT 0     -- 加班申请结束时间
    , ot_hours INT NOT NULL DEFAULT 0
    , ot_is_eating INT NOT NULL DEFAULT 0
    , ot_pending_hours INT NOT NULL DEFAULT 0   --加班待确认时长
    , ot_card_begin INT DEFAULT 0       --加班打卡开始时间
    , ot_card_end INT DEFAULT 0         --加班打卡结束时间

    , ot_min INT DEFAULT 0              --加班最低时长
    , ot_min_increment INT DEFAULT 0    --加班最低增量分钟
    , ot_allowance  INT DEFAULT 0   --加班津贴

    , ot_normal_hours INT DEFAULT 0       --平时加班时长
    , ot_weekend_hours INT DEFAULT 0      --周末加班时长
    , ot_holiday_hours INT DEFAULT 0      --法定节假日加班时长
    , ot_to_rest_hours INT DEFAULT 0      --加班转补休时长
    , ot_pending_hours_confirm INT DEFAULT 0   -- 加班确认总时长=平时加班时长+周末加班时长+法定节假日加班时长+加班转补休时长

    , ot_shift_sign_begin INT DEFAULT 0     --加班开始签到时间
    , ot_shift_sign_end INT DEFAULT 0       --加班结束签退时间
    , ot_shift_card_begin DATETIME   --加班打卡签到开始日期时间
    , ot_shift_card_end DATETIME     --加班打卡签退日期时间
--    , ot_repast_start_time INT DEFAULT 0     --加班就餐1开始时间
--    , ot_repast_end_time INT DEFAULT 0       --加班就餐1结束时间
--    , ot_repast_start_time2 INT DEFAULT 0    --加班就餐2开始时间
--    , ot_repast_end_time2 INT DEFAULT 0      --加班就餐2结束时间

--    , ot_repast_hours INT DEFAULT 0   --需要扣除的就餐时长，根据上下班打卡时间，判断是否包含就餐时间，计算就餐几次

    , leave_hours_total INT DEFAULT 0   --请假总时长
    , leave_type VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_hours VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_bill_id INT DEFAULT 0   -- 请假单ID

    , user_card_type INT     --员工考勤打卡类型

    --    , should_repast_count INT DEFAULT 0     --应就餐次数
    --    , can_repast INT DEFAULT 0   --是否可以就餐
    , is_swipe INT DEFAULT 0   -- 是否有打卡记录
    --    , is_swipe_correct  INT DEFAULT 0   -- 打卡是否正常


    , sign_begin_date DATETIME    -- 打卡记录开始日期时间
    , sign_end_date DATETIME      -- 打卡记录结束日期时间

    , sign_begin INT DEFAULT 0   -- 打卡记录开始时间
    , sign_end INT DEFAULT 0   -- 打卡记录结束时间

    , swipe_begin_repair INT DEFAULT 0   -- 上班打卡时间是补出勤
    , swipe_end_repair INT DEFAULT 0     -- 下班打卡时间是补出勤

    , flag INT DEFAULT 0   -- 操作标识
    , settle_date DATE    --结算年月
    , settle_year INT DEFAULT 0   --结算年份
    , settle_month  INT DEFAULT 0 --结算月份
    , late_hour INT DEFAULT 0 --迟到时长
    , early_hour  INT DEFAULT 0 --早退时长
    , off_duty_hour INT DEFAULT 0   --放班时长
  )

  -- 打卡记录临时表
  CREATE TABLE #cardData
  (
    user_serial BIGINT
    , sj DATETIME
    , mode INT
    , swipe_time INT DEFAULT 0
    , bh INT
    , prevBh INT
    , prevSj DATETIME
    , date_diff INT
    , is_valid INT DEFAULT 1
    , row_index INT
    , whether_validity INT
  )


  -- 打卡记录临时，用于更新表kt_jl状态
  CREATE TABLE #cardSave
  (
    bh INT
    , is_valid INT
  )

  CREATE TABLE #overtime(
    user_serial BIGINT,
    ot_bill_id  INT DEFAULT 0,
    ot_detail_id  INT,
    ot_shift_id INT,    --8+N/N+8生成加班单时把班次ID保存下来
    ot_date DATE,   --加班当天日期
    ot_date_work_status INT DEFAULT 0,  --工作日历状态：0 工作日，1 双休，2 节假
    ot_date2 DATE,  --跨天加班日期，明天或昨天
    ot_date2_work_status  INT DEFAULT 0,  --工作日历状态：0 工作日，1 双休，2 节假
    ot_begin  INT DEFAULT 0,
    ot_end  INT DEFAULT 0,
    ot_hours  INT DEFAULT 0,
    ot_hours_actual INT DEFAULT 0,
    ot_allowance  INT DEFAULT 0,
    ot_bill_status  INT DEFAULT 0,

    repast_start_time INT DEFAULT 0,     --就餐1开始时间
    repast_end_time INT DEFAULT 0,       --就餐1结束时间
    repast_first INT DEFAULT 0,        --是否就餐第一次

    repast_start_time2 INT DEFAULT 0,    --就餐2开始时间
    repast_end_time2 INT DEFAULT 0,      --就餐2结束时间
    repast_second INT DEFAULT 0,         --是否就餐第二次

    repast_hours INT DEFAULT 0,

    ot_time_begin DATETIME,   --加班申请开始日期时间
    ot_time_middle  DATETIME, --加班跨天时间24点日期
    ot_time_end DATETIME,     --加班申请结束日期时间
    ot_time_card_begin DATETIME,   --加班打卡时间取值范围开始日期时间
    ot_time_card_end DATETIME,      --加班打卡时间取值范围结束日期时间
    ot_time_swipe_begin  DATETIME, --加班实际打卡开始日期时间
    ot_time_swipe_end    DATETIME,  --加班实际打卡结束日期时间
    ot_swipe_middle INT DEFAULT 0,      --加班跨天时间24点时间
    ot_swipe_begin  INT DEFAULT 0,      --加班实际打卡开始时间
    ot_swipe_end  INT DEFAULT 0,         --加班实际打卡结束时间
    ot_calc_begin INT DEFAULT 0,        --加班计算开始时间
    ot_calc_end INT DEFAULT 0,          --加班计算结束时间
    ot_swipe_hours_ordinary  INT DEFAULT 0,      --平时加班时长
    ot_swipe_hours_weekend INT DEFAULT 0,    --周末加班时长
    ot_swipe_hours_statutory INT DEFAULT 0,     --节假日加班时长
    day_diff  INT DEFAULT 0,    --跨天间隔
    is_two INT DEFAULT 0,   --是否跨天
    work_calendar INT DEFAULT 0,   --工作日历
    is_83 INT DEFAULT 0   --是否8+N或N+8
  )

  CREATE TABLE #leave
  (
    user_serial INT
    , leave_type_id VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_type_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_hours INT
    , leave_status INT
    , leave_date DATETIME
    , leave_bill_id INT
    , is_valid  INT
  )

  CREATE TABLE #schedule
  (
    id INT, 
    user_serial BIGINT, 
    shift_id INT, 
    shift_date DATE, 
    shift_is_rest INT,
    row_no  INT
  )

  CREATE TABLE #users
  (
    user_serial BIGINT,
    user_no VARCHAR(50),
    user_name VARCHAR(200),
    dept_id BIGINT,
    dept_name VARCHAR(200),
    work_calendar INT DEFAULT 0, --工作日历
    work_status INT DEFAULT 0,    --工作状态
    lactational_begin DATE,      --授乳假有效期开始日期
    lactational_end DATE,        --授乳假有效期结束日期
    work_age INT DEFAULT 0
  )
 

  --获取要分析的员工
  IF (ISNULL(@users, '') = '')
  BEGIN

    INSERT INTO #timecard(user_serial, user_no, user_name, dept_id, dept_name, work_calendar, lactational_begin, lactational_end, work_age, work_status, card_date)
    SELECT user_serial, user_no, u.user_lname, user_dep, u.dep_name, user_work_calendar, lactational_begin, lactational_end, work_age,
      CASE WHEN wcd.is_statutory = 1 THEN 2 WHEN wcd.is_weekend = 1 THEN 1 ELSE 0 END, @date
    FROM bd_view_user_all u
    INNER JOIN (SELECT * FROM bd_kq_work_calendar_detail WHERE work_date = @date) wcd ON wcd.parent_id = u.user_work_calendar
    WHERE user_type <> 51

  END
  ELSE
  BEGIN

    INSERT INTO #timecard(user_serial, user_no, user_name, dept_id, dept_name, work_calendar, lactational_begin, lactational_end, work_age, work_status, card_date)
    SELECT user_serial, user_no, user_lname, u2.user_dep, dep_name, user_work_calendar, lactational_begin, lactational_end, work_age,
      CASE WHEN wcd.is_statutory = 1 THEN 2 WHEN wcd.is_weekend = 1 THEN 1 ELSE 0 END, @date
    FROM STRING_SPLIT(@users, ',') u
    INNER JOIN bd_view_user_all u2 ON u2.user_serial = u.value
    INNER JOIN (SELECT * FROM bd_kq_work_calendar_detail WHERE work_date = @date) wcd ON wcd.parent_id = u2.user_work_calendar
    WHERE u2.user_type <> 51

  END

  --结算年月
  DECLARE @settleYm DATE, @settleYear INT, @settleMonth INT
  SET @settleYm = dbo.bd_fn_getSettleDate(@date)
  SELECT @settleYear = YEAR(@settleYm), @settleMonth = MONTH(@settleYm)

  UPDATE #timecard SET settle_date = @settleYm
  UPDATE #timecard SET settle_year = @settleYear, settle_month = @settleMonth

  UPDATE #timecard SET card_week = (SELECT date_week_name FROM bd_sys_dim_date WHERE date = @date)

  INSERT INTO #users (user_serial)
  SELECT DISTINCT user_serial FROM #timecard 

  --封账的不能再计算考勤
  DELETE FROM #timecard 
  WHERE user_serial IN (SELECT user_serial FROM bd_kq_accounts WHERE [year] = @settleYear AND [month] = @settleMonth AND lock_status > 0)


  --==================================================排班数据==================================================


  INSERT INTO #schedule (id, user_serial, shift_id, shift_date, shift_is_rest, row_no)
  SELECT sch.id, sch.user_serial, sch.shift_id, sch.shift_date, sch.shift_is_rest, ROW_NUMBER() OVER (PARTITION BY sch.user_serial,shift_date ORDER BY sch.user_serial)
  FROM #timecard u
  INNER JOIN (SELECT id, user_serial, shift_id, shift_date, shift_is_rest FROM bd_kq_schedule_detail) sch ON sch.user_serial = u.user_serial
  WHERE sch.shift_date = @date 

 


  --排班数据，如果一个人一天多个班次，分开计算，一个班次一行记录(暂时没需求)
  --双休、节假日算加班，不计算排班

  UPDATE card SET card_date = @date
    , shift_id = sch.shift_id
    , shift_name = bc.mc
    , shift_begin = bc.sbsj
    , shift_end = bc.xbsj
    , shift_sign_begin = bc.sbks
    , shift_sign_end = bc.xbjs
    , shift_late = bc.cdsj
    , shift_early = bc.ztsj
    , shift_duty_on_delay = bc.bjcd
    , shift_duty_off_delay = bc.bjzt
    , shift_workday = bc.alld
    , shift_workday_hours = bc.allt
    , shift_is_flextime = bc.is_flextime
    , shift_flextime_interval_begin = bc.flextime_interval_begin
    , shift_flextime_interval_end = bc.flextime_interval_end
    , repast_start_time = ISNULL(bc.repast_start_time, 0)
    , repast_end_time = ISNULL(bc.repast_end_time, 0)
    , repast_start_time2 = ISNULL(bc.repast_start_time2, 0)
    , repast_end_time2 = ISNULL(bc.repast_end_time2, 0)
  FROM #timecard card
  INNER JOIN #schedule sch ON sch.user_serial = card.user_serial
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = sch.shift_id AND bc.lx = 0
  INNER JOIN bd_kq_work_calendar_detail wcd ON wcd.parent_id = card.work_calendar AND wcd.work_date = @date
  WHERE sch.row_no = 1 --AND card.work_status = 0
 
  --一天内第二次排班
--  INSERT INTO #timecard (user_serial, card_date, 
--    shift_id, shift_name, shift_begin, shift_end, shift_sign_begin,shift_sign_end,
--    shift_late, shift_early, shift_duty_on_delay, shift_duty_off_delay, shift_workday, shift_workday_hours,
--    shift_is_flextime, shift_flextime_interval_begin, shift_flextime_interval_end,
--    repast_start_time, repast_end_time, repast_start_time2, repast_end_time2)
--  SELECT sch.user_serial, @date
--    , sch.shift_id, bc.mc, bc.sbsj, bc.xbsj, bc.sbks, bc.xbjs
--    , bc.cdsj, bc.ztsj, bc.bjcd, bc.bjzt, bc.alld, bc.allt
--    , bc.is_flextime, bc.flextime_interval_begin, bc.flextime_interval_end
--    , ISNULL(bc.repast_start_time, 0), ISNULL(bc.repast_end_time, 0), ISNULL(bc.repast_start_time2, 0), ISNULL(bc.repast_end_time2, 0)
--  FROM #timecard card
--  INNER JOIN #schedule sch ON sch.user_serial = card.user_serial
--  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = sch.shift_id AND bc.lx = 0
--  INNER JOIN bd_kq_work_calendar_detail wcd ON wcd.parent_id = card.work_calendar AND wcd.work_date = @date
--  WHERE sch.row_no = 2 AND card.work_status = 0


--  UPDATE #timecard SET shift_name='', shift_begin = 0, shift_end = 0, should_hours = 0 WHERE work_status > 0

  --应出工时 
  UPDATE #timecard SET should_hours = CASE WHEN ISNULL(shift_id, 0) > 0 THEN shift_workday_hours ELSE 0 END

  --签到、签退日期时间
  UPDATE #timecard SET shift_card_begin = dbo.bd_fn_minToDateTime(shift_sign_begin, @date)
    , shift_card_end = dbo.bd_fn_minToDateTime(shift_sign_end, @date)
  WHERE ISNULL(shift_id, 0) > 0


  --多个班次排序


  --8+N，N+8班次
  UPDATE card
  SET ot_type_83 =
        CASE
          WHEN bc.sbsj >= card.shift_end THEN
            1
          WHEN bc.xbsj <= card.shift_begin THEN
            2
          ELSE
            0
        END
      ,shift_ot_begin = bc.sbsj, shift_ot_end = bc.xbsj
  FROM #timecard card
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = card.shift_id AND bc.lx = 1

--  UPDATE #timecard SET shift_begin = shift_ot_begin WHERE ot_type_83 = 2 AND work_status = 0
--  UPDATE #timecard SET shift_end = shift_ot_end WHERE ot_type_83 = 1 AND work_status = 0
  

--  --第二天的工作状态
--  UPDATE #timecard SET card_date_tomorrow = DATEADD(DAY, 1, @date)
--
--  UPDATE card SET work_status_tomorrow = CASE WHEN x.is_statutory = 1 THEN 2 WHEN x.is_weekend = 1 THEN 1 ELSE 0 END
--  FROM #timecard card
--  INNER JOIN (SELECT * FROM bd_kq_work_calendar_detail WHERE work_date = DATEADD(DAY, 1, @date)) x ON x.work_date = card.card_date_tomorrow


  UPDATE #timecard
  SET 
     shift_late_time_begin =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_begin, 0) + ISNULL(shift_duty_on_delay, 0), card_date)
          ELSE
            NULL
        END
     ,shift_late_time_end =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_late, 0), card_date)
          ELSE
            NULL
        END
     ,shift_early_time_begin =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_early, 0), card_date)
          ELSE
            NULL
        END
     ,shift_early_time_end =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_end, 0) - ISNULL(shift_duty_off_delay, 0), card_date)
          ELSE
            NULL
        END


  -- TODO 更新临时部门调整的部门ID
 


  --==================================================加班数据==================================================
  --8+3，1.5小时起，30分钟增量
  --其他加班，2小时起，30分钟增量 
  UPDATE #timecard
  SET ot_min =
        CASE
          WHEN ot_type_83 IN (1, 2) THEN
            90
          ELSE
            120
        END
     ,ot_min_increment =
        CASE
          WHEN ot_type_83 IN (1, 2) THEN
            30
          ELSE
            30
        END



  INSERT INTO #overtime (ot.user_serial, ot_bill_id, ot_shift_id, ot_date, ot_begin, ot_end, ot_hours, ot_bill_status, ot_time_begin, ot_time_end, ot_detail_id)
  SELECT ot.user_serial, order_id, shift_id, ot_date, ot_begin, ot_end, ot.jsjg, status, kssj, jssj, ot.detail_id
  FROM #users u
  INNER JOIN bd_view_overtime ot ON ot.user_serial = u.user_serial 
  WHERE ISNULL(ot.is_delete, 0) = 0 AND ISNULL(detail_is_delete, 0) = 0 AND status >= 3
    AND ot_date = @date


  --8+N加班
  UPDATE ot
  SET is_83 = 1, ot_shift_id = card.shift_id
  FROM #overtime ot
  INNER JOIN #timecard card ON ot.user_serial = card.user_serial AND card.shift_end = ot.ot_begin

  --N+8加班
  UPDATE ot
  SET is_83 = 2, ot_shift_id = card.shift_id
  FROM #overtime ot
  INNER JOIN #timecard card ON ot.user_serial = card.user_serial AND  card.shift_begin = ot.ot_end


  --打卡取值范围正负2小时
  UPDATE #overtime SET ot_time_card_begin = DATEADD(HOUR, -2, ot_time_begin), ot_time_card_end = DATEADD(HOUR, 2, ot_time_end)

  UPDATE #overtime SET day_diff = DATEDIFF(DAY, ot_date, ot_time_card_begin)

  UPDATE #overtime SET is_two = CASE WHEN day_diff <> 0 THEN 1 ELSE 0 END

  UPDATE #overtime SET ot_date2 = DATEADD(DAY, day_diff, ot_date)

  --工作日历
  UPDATE #overtime SET work_calendar = u.user_work_calendar
  FROM #overtime ot
  INNER JOIN (SELECT user_serial, user_work_calendar FROM bd_view_user_all) u ON u.user_serial = ot.user_serial

  --工作日历状态1
  UPDATE ot
  SET ot_date_work_status = CASE WHEN wcd.is_statutory = 1 THEN 2 WHEN wcd.is_weekend = 1 THEN 1 ELSE 0 END
  FROM #overtime ot
  INNER JOIN bd_kq_work_calendar_detail wcd ON wcd.parent_id = ot.work_calendar AND wcd.work_date = ot.ot_date

  --工作日历状态2
  UPDATE ot
  SET ot_date2_work_status = CASE WHEN wcd.is_statutory = 1 THEN 2 WHEN wcd.is_weekend = 1 THEN 1 ELSE 0 END
  FROM #overtime ot
  INNER JOIN bd_kq_work_calendar_detail wcd ON wcd.parent_id = ot.work_calendar AND wcd.work_date = ot.ot_date2


  UPDATE #overtime SET ot_time_middle = CONVERT(VARCHAR(10), DATEADD(DAY, 1, ot_time_card_begin), 120)

  UPDATE #overtime SET ot_swipe_middle = dbo.bd_fn_datetimeToInt(ot_date, ot_time_middle)

  --周末、节假日排班，获取班次,暂时用，正式数据班次在加班的时候会保存
--  UPDATE ot
--  SET ot_shift_id = sch.shift_id
--  FROM #overtime ot
--  INNER JOIN #schedule sch ON sch.user_serial = ot.user_serial
--  WHERE ot.ot_date_work_status > 0

  --班次就餐时间
  UPDATE #overtime SET repast_start_time = ISNULL(bc.repast_start_time, 0), repast_end_time = ISNULL(bc.repast_end_time, 0), repast_first = 0, 
    repast_start_time2 = ISNULL(bc.repast_start_time2, 0), repast_end_time2 = ISNULL(bc.repast_end_time2, 0), repast_second = 0
  FROM #overtime ot
  INNER JOIN bd_view_bc_sd bc ON bc.bc_id = ot.ot_shift_id

  UPDATE #overtime SET repast_start_time = IIF(repast_start_time = 0, 9999, repast_start_time), repast_end_time = IIF(repast_end_time = 0, 9999, repast_end_time),
    repast_start_time2 = IIF(repast_start_time2 = 0, 9999, repast_start_time2), repast_end_time2 = IIF(repast_end_time2 = 0, 9999, repast_end_time2)


  --加班最小开始时间、最大结束时间，加班时间都计算在第一个班次
  UPDATE card SET ot_shift_card_begin = ot_date_min, ot_shift_card_end = ot_date_max
  FROM #timecard card 
  INNER JOIN (
    SELECT user_serial, MIN(ot_time_card_begin) ot_date_min, MAX(ot_time_card_end) ot_date_max
    FROM #overtime
    GROUP BY user_serial
  ) x ON x.user_serial = card.user_serial 
  WHERE card.card_no = 1


  UPDATE #timecard SET is_ot = 1 WHERE user_serial IN (SELECT user_serial FROM #overtime)


  --上班打卡结束日期时间范围
  --下班打卡开始日期时间范围
  UPDATE #timecard
  SET shift_begin_swipe_end =
        CASE
          WHEN ISNULL(shift_id, 0) = 0 THEN
            ot_shift_card_begin
          ELSE
            shift_late_time_begin
        END
     ,shift_end_swipe_begin =
        CASE
          WHEN ISNULL(shift_id, 0) = 0  THEN
            ot_shift_card_end
          ELSE
            shift_early_time_begin
        END

  --==============================================================打卡记录开始=======================================================

  --打卡开始时间、结束时间
  UPDATE #timecard
  SET sign_begin =
        CASE
          WHEN ISNULL(is_ot, 0) = 1 AND ot_begin < shift_sign_begin THEN
            ot_begin
          ELSE
            shift_sign_begin
        END
     ,sign_end = 
        CASE
          WHEN ISNULL(is_ot, 0) = 1 AND ot_end > shift_sign_end THEN
            ot_end
          ELSE
            shift_sign_end
        END


    --打卡开始日期时间、打卡结束日期时间
  UPDATE #timecard SET sign_begin_date = ISNULL(shift_card_begin, ot_shift_card_begin), sign_end_date = ISNULL(shift_card_end, ot_shift_card_end)

  UPDATE #timecard SET sign_begin_date = IIF(shift_card_begin > ot_shift_card_begin, ot_shift_card_begin, shift_card_begin) 
  WHERE shift_card_begin IS NOT NULL AND ot_shift_card_begin IS NOT NULL

  UPDATE #timecard SET sign_end_date = IIF(shift_card_end > ot_shift_card_end, shift_card_end, ot_shift_card_end) 
  WHERE shift_card_end IS NOT NULL AND ot_shift_card_end IS NOT NULL


  -- 部门总监的打卡时间取0-24小时
  UPDATE #timecard
  SET sign_begin_date = dbo.bd_fn_minToDateTime(0, card_date)
     ,shift_card_end = dbo.bd_fn_minToDateTime(1439, card_date)
  WHERE user_card_type = 22


  DECLARE @cardBegin VARCHAR(50)
         ,@cardEnd VARCHAR(50)


  -- 取最小时间和最大时间
  SELECT @cardBegin = MIN(sign_begin_date), @cardEnd = MAX(sign_end_date) FROM #timecard 

  IF (ISNULL(@cardBegin, '') = ''
    OR
    ISNULL(@cardEnd, '') = '')
  BEGIN
    SET @cardBegin = CONVERT(VARCHAR(10), @date, 120)
    SET @cardEnd = CONVERT(VARCHAR(10), @date, 120) + ' 23:59:59'
  END


  --取最小和最大时间范围内的打卡记录
  INSERT INTO #cardData(user_serial, sj, mode, swipe_time, bh, prevBh, prevSj, date_diff, whether_validity)
  SELECT card.user_serial
   , CONVERT(VARCHAR(16), jl.sj, 120)
   , jl.mode
   , dbo.bd_fn_datetimeToInt(@date, sj)
   , jl.bh
   , LAG(bh, 1, 0) OVER (PARTITION BY card.user_serial ORDER BY sj ASC)
   , LAG(sj, 1, 0) OVER (PARTITION BY card.user_serial ORDER BY sj ASC)
   , 0
   , jl.whether_validity
  FROM #timecard card
  INNER JOIN kt_jl jl ON jl.user_serial = card.user_serial
  WHERE sj >= @cardBegin
    AND sj <= @cardEnd
    AND whether_validity IN (0, 1, 3, 4)

  UPDATE #cardData
  SET date_diff = DATEDIFF(MINUTE, prevSj, sj)


  --  0未处理
  --  1手动有效
  --  2手动无效 
  --  3系统有效
  --  4系统无效

  -- 30分钟内连续打卡无效
  UPDATE #cardData
  SET is_valid = 0
  WHERE date_diff <= 30

  UPDATE card
  SET row_index = x.RowNum
  FROM #cardData card
  INNER JOIN (
      SELECT
        *, RowNum = ROW_NUMBER() OVER (PARTITION BY user_serial ORDER BY sj)
      FROM #cardData
      WHERE is_valid = 1
    ) x ON x.bh = card.bh

--  -- 奇偶算法， 只取最前面的2条数据作为有效打卡，其他全部无效，手动补出勤的状态不变
--  UPDATE #cardData
--  SET is_valid = 0
--  WHERE row_index > 2
--
--  UPDATE #cardData
--  SET is_valid = 1
--  WHERE row_index <= 2
--
--  UPDATE #cardData
--  SET whether_validity =
--    CASE
--      WHEN ISNULL(is_valid, 0) = 0 THEN
--        4
--      ELSE
--        3
--    END



  -- 把打卡记录状态保存在临时表中，最后更新物理表kt_jl
  INSERT INTO #cardSave(bh, is_valid)
  SELECT bh, ISNULL(whether_validity, 0) FROM #cardData

  -- 删除无效数据，用于计算考勤
  DELETE FROM #cardData
  WHERE is_valid = 0



  --==============================================================打卡记录结束=======================================================


  --==============================================================打卡数据分析开始=======================================================


  --获取第一次打卡和最后一次打卡
  UPDATE card
  SET swipe_first = dbo.bd_fn_datetimeToInt(@date, x.first_swipe)
     ,swipe_last = dbo.bd_fn_datetimeToInt(@date, x.last_swipe)
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, MIN(sj) first_swipe, MAX(sj) last_swipe
      FROM (
        SELECT cd.user_serial, cd.sj, cd.mode, cd.swipe_time, cd.bh, cd.prevBh, cd.prevSj, cd.date_diff, cd.is_valid, cd.row_index, cd.whether_validity
        FROM #timecard card
        INNER JOIN #cardData cd ON cd.user_serial = card.user_serial
        WHERE sj >= card.sign_begin_date AND sj <= card.sign_end_date
      ) a
      GROUP BY user_serial
    ) x ON x.user_serial = card.user_serial



  --迟到打卡时间
  UPDATE card
  SET swipe_late = CASE WHEN x.swipe_begin IS NOT NULL THEN dbo.bd_fn_datetimeToInt(@date, x.swipe_begin) ELSE NULL END
  FROM #timecard card
  INNER JOIN (
      SELECT card.user_serial, MIN(sj) swipe_begin
      FROM #timecard card
      INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE kt.sj > card.shift_late_time_begin
        AND sj < card.shift_late_time_end
        AND ISNULL(card.shift_id, 0) > 0
        -- 工作日班次计算迟到，加班不计算
        AND card.work_status = 0
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial
 

  --上班打卡时间
  --工作日时间范围：签到开始日期时间--X分钟内不计迟到
  --加班时间范围：签到开始日期时间--加班上班开始日期时间
  UPDATE card
  SET swipe_begin = dbo.bd_fn_datetimeToInt(@date, x.swipe_begin)
  FROM #timecard card
  INNER JOIN (
      SELECT card.user_serial, MAX(sj) swipe_begin
      FROM #timecard card
      INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE sj >= card.sign_begin_date
        AND sj <= card.shift_begin_swipe_end
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial


  --早退打卡时间
  UPDATE card
  SET swipe_early = dbo.bd_fn_datetimeToInt(@date, x.swipe_begin)
  FROM #timecard card
  INNER JOIN (
      SELECT card.user_serial, MIN(sj) swipe_begin
      FROM #timecard card
      INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE kt.sj > card.shift_early_time_begin
        AND sj < card.shift_early_time_end
        AND ISNULL(card.shift_id, 0) > 0
        AND card.work_status = 0
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial


  --下班打卡时间，取离下班时间最近的打卡记录
  --工作日时间范围：X分钟内不计早退--签退时间
  --加班时间范围：加班下班时间--签退时间
  UPDATE card
  SET swipe_end = dbo.bd_fn_datetimeToInt(@date, x.swipe_first)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MIN(sj) swipe_first
      FROM #timecard card
    LEFT JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE sj >= card.shift_end_swipe_begin
        AND sj <= card.sign_end_date
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial



  --部门总监
  --上班时长大于8小时正常,一天不够8小时提示异常，扣除吃饭时间（半小时）
  --打卡时间取当天24小时内的最早打卡时间和最晚打卡时
  --user_card_type=22
  UPDATE #timecard
  SET swipe_begin = swipe_first
     ,swipe_end = swipe_last
  WHERE user_card_type = 22

--SELECT swipe_end_actual,swipe_begin_actual,swipe_begin,swipe_end,actual_hours, swipe_first, t.swipe_last FROM #timecard t

  UPDATE #timecard SET swipe_begin = swipe_first WHERE ISNULL(swipe_begin, 0) = 9999 AND ISNULL(shift_id, 0) > 0
  UPDATE #timecard SET swipe_end = swipe_last WHERE ISNULL(swipe_end, 0) = 9999 AND ISNULL(shift_id, 0) > 0
  
--SELECT swipe_end_actual,swipe_begin_actual,swipe_begin,swipe_end,actual_hours, swipe_first, t.swipe_last FROM #timecard t

  UPDATE #timecard SET swipe_time_begin = dbo.bd_fn_minToDateTime(swipe_begin, @date) WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) < 9999

  UPDATE #timecard SET swipe_time_end = dbo.bd_fn_minToDateTime(swipe_end, @date) WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) < 9999

  --迟到结束日期时间
  UPDATE #timecard SET swipe_time_late = dbo.bd_fn_minToDateTime(shift_begin + shift_duty_on_delay, @date) WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) < 9999

  --早退开始日期时间
  UPDATE #timecard SET swipe_time_early = dbo.bd_fn_minToDateTime(shift_end - shift_duty_off_delay, @date) WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) < 9999


    UPDATE #timecard SET swipe_begin_actual = swipe_begin WHERE ISNULL(swipe_begin, 0) < 9999
    
    --签到开始时间与上班开始时间+X分钟内不计迟到之间打卡，打卡开始时间为班次上班时间
    UPDATE #timecard SET swipe_begin_actual = shift_begin WHERE ISNULL(swipe_begin, 0) < 9999 AND swipe_time_begin >= shift_card_begin AND swipe_time_begin <= swipe_time_late

    --上班开始时间+X分钟内不计迟到之间 与 早退开始之前打卡，打卡开始时间以实际打卡时间为准
--    UPDATE #timecard SET swipe_begin_actual = swipe_begin WHERE ISNULL(swipe_begin, 0) > shift_begin + shift_duty_on_delay AND ISNULL(swipe_begin, 0) < shift_end - shift_duty_on_delay

    --弹性上班

    UPDATE #timecard SET swipe_end_actual = ISNULL(swipe_end, 0) WHERE ISNULL(swipe_end, 0) < 9999

    --下班结束时间-X分钟内不计早退 与 签退时间之间打卡，打卡结束时间为班次下班时间
    UPDATE #timecard SET swipe_end_actual = shift_end WHERE ISNULL(swipe_end, 0) < 9999 AND ISNULL(swipe_end, 0) > shift_end - shift_duty_off_delay AND ISNULL(swipe_end, 0) <= shift_sign_end

    --弹性下班
   
   
--    UPDATE #timecard SET swipe_begin_actual = 0 WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) > 0

--  -- 弹性，少于15分钟，计算为15分钟
--  UPDATE #timecard
--  SET swipe_begin_actual = IIF(swipe_begin_actual % 15 = 0, swipe_begin_actual, (swipe_begin_actual / 15 * 15 + 15))
--  WHERE shift_is_flextime = 1 AND user_card_type = 21

  --==============================================================打卡数据分析结束=======================================================

  --==============================================================加班数据分析开始=======================================================
--SELECT * FROM #cardData
  --打卡开始日期时间、打卡结束日期时间
--  UPDATE #overtime SET ot_time_swipe_begin = (SELECT MIN(sj) FROM #cardData WHERE user_serial = user_serial AND sj >= ot_time_card_begin AND sj <= DATEADD(HOUR, 1, ot_time_begin))
--    , ot_time_swipe_end = (SELECT MAX(sj) FROM #cardData WHERE user_serial = user_serial AND sj >= DATEADD(HOUR, -1, ot_time_end) AND sj <= ot_time_card_end)

  UPDATE #overtime SET ot_time_swipe_begin = (SELECT MIN(sj) FROM #cardData  card WHERE card.user_serial = user_serial AND sj >= ot_time_card_begin AND sj <= ot_time_card_end)
    , ot_time_swipe_end = (SELECT MAX(sj) FROM #cardData card WHERE card.user_serial = user_serial AND sj >= ot_time_card_begin AND sj <= ot_time_card_end)



  UPDATE #overtime SET ot_time_swipe_begin = ot_time_begin WHERE is_83 = 1 AND ot_date_work_status = 0

  UPDATE #overtime SET ot_time_swipe_end = ot_time_end WHERE is_83 = 2 AND ot_date_work_status = 0

  UPDATE #overtime SET ot_swipe_middle = dbo.bd_fn_datetimeToInt(ot_date, ot_time_middle)

  UPDATE #overtime SET ot_swipe_begin = dbo.bd_fn_datetimeToInt(@date, ot_time_swipe_begin),
    ot_swipe_end = dbo.bd_fn_datetimeToInt(@date, ot_time_swipe_end)

  UPDATE #overtime SET ot_calc_begin = CASE WHEN IIF(ot_begin < 0, ABS(ot_swipe_begin), ot_swipe_begin) <= IIF(ot_begin < 0, ABS(ot_begin), ot_begin) THEN ot_begin ELSE ot_swipe_begin END,
    ot_calc_end = CASE WHEN ot_swipe_end >= ot_end THEN ot_end ELSE ot_swipe_end END

  --第一次就餐是否使用
  UPDATE #overtime
  SET repast_first =
    CASE
      WHEN ot_swipe_begin <= repast_start_time AND
        ot_swipe_end >= repast_end_time THEN
        1
      ELSE
        0
    END
  WHERE ISNULL(ot_shift_id, 0) > 0 AND ISNULL(repast_start_time, 0) > 0 AND ISNULL(repast_end_time, 0) > 0 
    
  --第一次就餐扣30分钟
  UPDATE #overtime SET repast_hours = repast_hours + 30 WHERE repast_first = 1

  --第二次就餐是否使用
  UPDATE #overtime
  SET repast_second =
    CASE
      WHEN ot_swipe_begin <= repast_start_time2 AND
        ot_swipe_end >= repast_end_time2 THEN
        1
      ELSE
        0
    END
  WHERE ISNULL(ot_shift_id, 0) > 0 AND ISNULL(repast_start_time2, 0) > 0 AND ISNULL(repast_end_time2, 0) > 0  

  --第二次就餐扣30分钟
  UPDATE #overtime SET repast_hours = repast_hours + 30 WHERE repast_second = 1

  --加班当天以外的平时加班
  UPDATE #overtime
  SET ot_swipe_hours_ordinary =
    CASE
      WHEN ot_date <= ot_date2 THEN
        ot_swipe_hours_ordinary + (ot_swipe_middle - ABS(ot_calc_begin))
      WHEN ot_date > ot_date2 THEN
        ot_swipe_hours_ordinary + IIF(ot_calc_begin < 0, 1440 + ot_calc_begin, 0) - IIF(ot_calc_begin <= repast_start_time AND ot_swipe_middle >= repast_end_time, repast_hours, 0)
      ELSE
        ot_swipe_hours_ordinary
    END
  WHERE ot_date2_work_status = 0 --AND ISNULL(ot_calc_begin, 0) >= 0 AND ISNULL(ot_calc_end, 0) >= 0

  --加班当天以外的双休加班
  UPDATE #overtime
  SET ot_swipe_hours_weekend =
    CASE
      WHEN ot_date <= ot_date2 THEN
        ot_swipe_hours_weekend + (ot_swipe_middle - ABS(ot_calc_begin))
      WHEN ot_date > ot_date2 THEN
        ot_swipe_hours_weekend + IIF(ot_calc_begin < 0, 1440 + ot_calc_begin, 0) - IIF(ot_calc_begin <= repast_start_time AND ot_swipe_middle >= repast_end_time, repast_hours, 0)
      ELSE
        ot_swipe_hours_weekend
    END
  WHERE ot_date2_work_status = 1 --AND ISNULL(ot_calc_begin, 0) >= 0 AND ISNULL(ot_calc_end, 0) >= 0

  --加班当天以外的节假日加班
  UPDATE #overtime
  SET ot_swipe_hours_statutory =
    CASE
      WHEN ot_date <= ot_date2 THEN
        ot_swipe_hours_statutory + (ot_swipe_middle - ABS(ot_calc_begin))
      WHEN ot_date > ot_date2 THEN
        ot_swipe_hours_statutory + IIF(ot_calc_begin < 0, 1440 + ot_calc_begin, 0) - IIF(ot_calc_begin <= repast_start_time AND ot_swipe_middle >= repast_end_time, repast_hours, 0)
      ELSE
        ot_swipe_hours_statutory
    END
  WHERE ot_date2_work_status = 2 --AND ISNULL(ot_calc_begin, 0) >= 0 AND ISNULL(ot_calc_end, 0) >= 0

  --加班当天的平时加班
  UPDATE #overtime
  SET ot_swipe_hours_ordinary = ot_swipe_hours_ordinary + (ot_calc_end - ot_swipe_middle) - IIF(ot_calc_begin <= repast_start_time AND ot_swipe_middle >= repast_end_time, repast_hours, 0)
  WHERE ot_date_work_status = 0 --AND ISNULL(ot_calc_begin, 0) >= 0 AND ISNULL(ot_calc_end, 0) >= 0

  --加班当天的双休加班
  UPDATE #overtime
  SET ot_swipe_hours_weekend = ot_swipe_hours_weekend + (ot_calc_end - ot_swipe_middle) - IIF(ot_calc_begin <= repast_start_time AND ot_swipe_middle >= repast_end_time, repast_hours, 0)
  WHERE ot_date_work_status = 1 --AND ISNULL(ot_calc_begin, 0) >= 0 AND ISNULL(ot_calc_end, 0) >= 0

  --加班当天的节假日加班
  UPDATE #overtime
  SET ot_swipe_hours_statutory = ot_swipe_hours_statutory + (ot_calc_end - ot_swipe_middle) - IIF(ot_calc_begin <= repast_start_time AND ot_swipe_middle >= repast_end_time, repast_hours, 0)
  WHERE ot_date_work_status = 2 --AND ISNULL(ot_calc_begin, 0) >= 0 AND ISNULL(ot_calc_end, 0) >= 0

  UPDATE #overtime SET ot_swipe_hours_ordinary = 0 WHERE ISNULL(ot_swipe_hours_ordinary, 0) <= 0
  UPDATE #overtime SET ot_swipe_hours_weekend = 0 WHERE ISNULL(ot_swipe_hours_weekend, 0) <= 0
  UPDATE #overtime SET ot_swipe_hours_statutory = 0 WHERE ISNULL(ot_swipe_hours_statutory, 0) <= 0

  --小于30分钟不计
  UPDATE #overtime SET ot_swipe_hours_ordinary = IIF(ot_swipe_hours_ordinary % 30 = 0, ot_swipe_hours_ordinary, ot_swipe_hours_ordinary - ot_swipe_hours_ordinary % 30) WHERE ISNULL(ot_swipe_hours_ordinary, 0) > 0
  UPDATE #overtime SET ot_swipe_hours_weekend = IIF(ot_swipe_hours_weekend % 30 = 0, ot_swipe_hours_weekend, ot_swipe_hours_weekend - ot_swipe_hours_weekend % 30) WHERE ISNULL(ot_swipe_hours_weekend, 0) > 0
  UPDATE #overtime SET ot_swipe_hours_statutory = IIF(ot_swipe_hours_statutory % 30 = 0, ot_swipe_hours_statutory, ot_swipe_hours_statutory - ot_swipe_hours_statutory % 30) WHERE ISNULL(ot_swipe_hours_statutory, 0) > 0

  UPDATE #overtime SET ot_hours_actual = ISNULL(ot_swipe_hours_ordinary, 0) + ISNULL(ot_swipe_hours_weekend, 0) + ISNULL(ot_swipe_hours_statutory, 0)

--SELECT * FROM #overtime
--  WHERE user_serial=20001533

  --更新加班时间
  UPDATE card
  SET ot_normal_hours = ISNULL(ot_swipe_hours_ordinary, 0)
     ,ot_weekend_hours = ISNULL(ot_swipe_hours_weekend, 0)
     ,ot_holiday_hours = ISNULL(ot_swipe_hours_statutory, 0)
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, SUM(ISNULL(ot_swipe_hours_ordinary, 0)) ot_swipe_hours_ordinary, 
        SUM(ISNULL(ot_swipe_hours_weekend, 0)) ot_swipe_hours_weekend, 
        SUM(ISNULL(ot_swipe_hours_statutory, 0)) ot_swipe_hours_statutory
      FROM #overtime
      GROUP BY user_serial
    ) x ON x.user_serial = card.user_serial

  UPDATE #timecard SET ot_pending_hours = ISNULL(ot_normal_hours, 0) + ISNULL(ot_weekend_hours, 0) + ISNULL(ot_holiday_hours, 0)

  --更新加班打卡时间，先以最早打卡和最晚打卡
  UPDATE card
  SET ot_card_begin = ISNULL(ot_swipe_begin, 0)
     ,ot_card_end = ISNULL(ot_swipe_end, 0)
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, MIN(ot_swipe_begin) ot_swipe_begin, MAX(ot_swipe_end) ot_swipe_end
      FROM #overtime
      GROUP BY user_serial
    ) x ON x.user_serial = card.user_serial     


  --加班饭贴, 8+N，N+8和加班申请分开计算
  --  3.5<=加班小时<11.5    210-690
  --  11.5<=加班小时<24     690-1440


--    UPDATE #timecard SET ot_allowance = ISNULL(ot_allowance, 0) + 1 WHERE ot_pending_hours >= 210 AND ot_pending_hours < 690
--    UPDATE #timecard SET ot_allowance = ISNULL(ot_allowance, 0) + 2 WHERE ot_pending_hours >= 690 AND ot_pending_hours < 1440   
  
  UPDATE #overtime SET ot_allowance = ISNULL(ot_allowance, 0) + 1 WHERE ot_hours_actual >= 210 AND ot_hours_actual < 690
  UPDATE #overtime SET ot_allowance = ISNULL(ot_allowance, 0) + 2 WHERE ot_hours_actual >= 690 AND ot_hours_actual < 1440  

  UPDATE card
  SET ot_allowance = ISNULL(ot.ot_allowance, 0)
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, SUM(ot_allowance) ot_allowance
      FROM #overtime
      GROUP BY user_serial
    ) ot ON ot.user_serial = card.user_serial
  

  --==============================================================加班数据分析结束=======================================================



  --==================================================计算就餐时长==================================================

  --==================================================排班就餐时长==================================================

  --第一次就餐是否使用
  UPDATE #timecard
  SET repast_first =
    CASE
      WHEN swipe_begin <= repast_start_time AND
        swipe_end >= repast_end_time THEN
        1
      ELSE
        0
    END
  WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(repast_start_time, 0) > 0 AND ISNULL(repast_end_time, 0) > 0 
    
  --第一次就餐扣30分钟
  UPDATE #timecard SET repast_hours = repast_hours + 30 WHERE repast_first = 1

  --第二次就餐是否使用
  UPDATE #timecard
  SET repast_second =
    CASE
      WHEN swipe_begin <= repast_start_time2 AND
        swipe_end >= repast_end_time2 THEN
        1
      ELSE
        0
    END
  WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(repast_start_time2, 0) > 0 AND ISNULL(repast_end_time2, 0) > 0  

  --第二次就餐扣30分钟
  UPDATE #timecard SET repast_hours = repast_hours + 30 WHERE repast_second = 1



  --==================================================实出时长==================================================

  UPDATE #timecard SET actual_hours = 0 WHERE ISNULL(swipe_begin, 0) = 9999 OR ISNULL(swipe_end, 0) = 9999

  --弹性
--SELECT swipe_end_actual,swipe_begin_actual,swipe_begin,swipe_end,actual_hours FROM #timecard t
  UPDATE #timecard SET actual_hours = swipe_end_actual - swipe_begin_actual - repast_hours WHERE ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) < 9999 AND ISNULL(swipe_end, 0) < 9999
--SELECT swipe_end_actual,swipe_begin_actual,swipe_begin,swipe_end,actual_hours FROM #timecard t

  UPDATE #timecard SET actual_hours = 0 WHERE ISNULL(actual_hours, 0) < 0

  --当实出工时>=应出工时，把实出工时设置为应出工时
  UPDATE #timecard SET actual_hours = should_hours WHERE actual_hours >= should_hours
 


  --开始时间	结束时间		
  --16:30	    20:00	    0.5	中班津贴        990   1200
  --20:00	    23:00	    0.5	中班津贴        1200  1380
  --0:00	    6:00	    1	  夜班津贴        0     360
  --4:00	    10:00		      早早班津贴      240   600

  UPDATE #timecard SET shift_allowance_zb1 = shift_allowance_zb1 + 0.5 WHERE swipe_first <= 990 AND swipe_last >= 1200 AND sign_begin >= 0 AND actual_hours > 0
  UPDATE #timecard SET shift_allowance_zb1 = shift_allowance_zb1 + 0.5 WHERE ABS(swipe_first) <= 990 AND ABS(swipe_last) >= -1200 AND sign_begin < 0 AND actual_hours > 0

  UPDATE #timecard SET shift_allowance_zb2 = shift_allowance_zb2 + 0.5 WHERE swipe_first <= 1200 AND swipe_last >= 1380  AND sign_begin >= 0 AND actual_hours > 0
  UPDATE #timecard SET shift_allowance_zb2 = shift_allowance_zb2 + 0.5 WHERE ABS(swipe_first) <= 1200 AND ABS(swipe_last) >= -1380 AND sign_begin < 0 AND actual_hours > 0


  UPDATE #timecard SET shift_allowance_yb = 1 WHERE swipe_first <= 0 AND swipe_last >= 360 AND actual_hours > 0


  --==================================================请假数据==================================================

  INSERT INTO #leave (leave.user_serial, leave_type_id, leave_type_name, leave_hours, leave_status, leave_date, leave_bill_id, is_valid)
  SELECT leave.user_serial, leave_id, leave_type_name, jsjg, status, kssj, order_id, 1
  FROM #users u
  INNER JOIN bd_view_leave leave ON leave.user_serial = u.user_serial
  WHERE kssj = @date AND status <> 2
        AND ISNULL(is_del, 0) = 0 AND ISNULL(leave.whether_validity, 0) = 1

  --放班时长
  UPDATE card
  SET off_duty_hour = x.leave_hours
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, leave_hours
      FROM #leave
      WHERE leave_type_id = '10003' AND leave_status IN (3, 4)
    ) x ON x.user_serial = card.user_serial


  --实际工时大于应出工时，授乳假无效，不参与考勤计算
  UPDATE #leave SET is_valid = 0 WHERE leave_type_id = '10018' AND user_serial IN (SELECT user_serial FROM #timecard WHERE actual_hours >= should_hours) 

  --请假总时长
  UPDATE card
  SET leave_hours_total = x.total
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, SUM(ISNULL(leave_hours, 0)) total
      FROM #leave
      WHERE leave_status IN (3, 4) AND is_valid = 1
      GROUP BY user_serial
    ) x ON x.user_serial = card.user_serial


  --请假假类名称
  UPDATE card
  SET leave_type = x.leave_type
     ,leave_hours = x.leave_hours
  FROM #timecard card
  INNER JOIN (
      SELECT
        b.user_serial, STUFF((SELECT '| ' + CAST(a.leave_type_name AS VARCHAR) FROM #leave a WHERE b.user_serial = a.user_serial AND leave_status IN (3, 4) FOR XML PATH ('')), 1, 1, '') leave_type, STUFF((SELECT '| ' + CAST(dbo.bd_fn_numToMin2(a.leave_hours) AS VARCHAR) FROM #leave a WHERE b.user_serial = a.user_serial AND leave_status IN (3, 4) FOR XML PATH ('')), 1, 1, '') leave_hours
      FROM #leave b
      WHERE leave_status IN (3, 4) AND is_valid = 1
      GROUP BY b.user_serial
    ) x ON x.user_serial = card.user_serial
  

--  --==================================================请假数据分析结束==================================================

  --==================================================考勤状态 开始==================================================
  --正常打卡            0
  --处理后正常          2,3,15
  --处理正常(请假)     2
  --处理正常(补出勤)   3
  --迟到    4
  --早退    5
  --缺勤    6
  --加班异常  7
  --处理正常(请假) + 处理正常(补出勤)    15

  --迟到
  UPDATE #timecard SET status = 4 WHERE ISNULL(swipe_late, 0) > 0 AND ISNULL(shift_id, 0) > 0 AND shift_is_flextime = 0 AND work_status = 0

  --早退
  UPDATE #timecard SET status = 5 WHERE ISNULL(swipe_early, 0) > 0 AND ISNULL(shift_id, 0) > 0 AND shift_is_flextime = 0 AND work_status = 0


  --旷工

    --上班打卡大于班次开始时间+x分钟内不计迟到
  UPDATE #timecard SET status = 6 WHERE ISNULL(shift_id, 0) > 0 AND swipe_begin > shift_begin + shift_duty_on_delay AND status NOT IN(4, 5) AND work_status = 0

    --下班打卡小于班次下班时间-x分钟内不计早退
  UPDATE #timecard SET status = 6 WHERE ISNULL(shift_id, 0) > 0 AND swipe_end < shift_end - shift_duty_on_delay AND status NOT IN(4, 5) AND work_status = 0

    --上班或下班没打卡
  UPDATE #timecard SET status = 6 WHERE ISNULL(shift_id, 0) > 0 AND (ISNULL(swipe_begin, 0) = 9999 OR ISNULL(swipe_end, 0) = 9999) AND status NOT IN(4, 5) AND work_status = 0

  UPDATE #timecard SET status = 6 WHERE ISNULL(shift_id, 0) > 0 AND (ISNULL(swipe_begin, 0) < 0  OR ISNULL(swipe_end, 0) < 0) AND status NOT IN(4, 5) AND work_status = 0

--  UPDATE #timecard SET status = 0 WHERE ISNULL(shift_id, 0) = 0

  UPDATE #timecard SET status = 0 WHERE ISNULL(shift_id, 0) > 0 AND should_hours > 0 AND actual_hours + leave_hours_total >= should_hours  AND work_status = 0


  --==================================================考勤状态 结束==================================================


  --计算实际出勤天数，有的班次是7.5小时，按一天8小时计算天数的时候，会有小数，如果出勤正常，7.5小时计为8小时，统计时使用
  UPDATE #timecard SET actual_hours_calc = 480 WHERE should_hours > 0 AND should_hours = actual_hours AND work_status = 0
  UPDATE #timecard SET actual_hours_calc = actual_hours WHERE should_hours > 0 AND should_hours > actual_hours AND work_status = 0

  UPDATE #timecard SET should_hours_calc = 480 WHERE should_hours > 0 AND should_hours <= 480 AND work_status = 0

  --加班的时间不在正常班次字段显示
  UPDATE #timecard SET shift_begin = 9999, shift_end = 9999, swipe_begin = 9999, swipe_end = 9999, should_hours = 0, actual_hours = 0 WHERE work_status > 0

  
  UPDATE #timecard SET late_hour = CASE WHEN status = 4 THEN swipe_begin - shift_begin ELSE 0 END
    , early_hour = CASE WHEN status = 5 THEN swipe_end - shift_end ELSE 0 END


  --  10018	授乳假
  IF EXISTS(
  	SELECT 1
  	FROM #leave WHERE leave_type_id = '10018'
  ) 
  BEGIN
    --实际工时大于应出工时，系统取消授乳假，并返还额度
    DECLARE @json VARCHAR(MAX) = ''
    SET @json = (SELECT * FROM #leave WHERE leave_type_id = '10018' AND user_serial IN (SELECT user_serial FROM #timecard WHERE actual_hours >= should_hours) FOR JSON AUTO)

    EXEC bd_proc_leave_cancel_lactation @json

    DELETE FROM #leave WHERE leave_type_id = '10018' AND user_serial IN (SELECT user_serial FROM #timecard WHERE actual_hours >= should_hours) 
  END


--  SELECT * FROM #overtime WHERE user_serial = 20010963
--  SELECT * FROM #timecard WHERE user_serial = 20010963


  BEGIN TRY
    BEGIN TRANSACTION

    -- 更新考勤卡表数据
    MERGE
    INTO bd_kq_timecard p
    USING #timecard s ON p.user_serial = s.user_serial
      AND p.card_date = s.card_date
    WHEN MATCHED
      THEN UPDATE SET
          p.user_no = s.user_no
          , p.user_name = s.user_name
          , p.dept_id = s.dept_id
          , p.dept_name = s.dept_name
          , p.card_week = s.card_week
          , p.shift_id = s.shift_id
          , p.shift_name = s.shift_name
          , p.shift_begin = s.shift_begin
          , p.shift_end = s.shift_end
          , p.swipe_begin = ISNULL(s.swipe_begin, 0)
          , p.swipe_end = ISNULL(s.swipe_end, 0)
          , p.should_hours = s.should_hours
          , p.actual_hours = s.actual_hours
          , p.total_hours = s.total_hours
          , p.status = s.status
          , p.pending_hours = s.pending_hours
          , p.ot_type_id = s.ot_type_id
          , p.ot_type_name = s.ot_type_name
          , p.ot_begin = s.ot_begin
          , p.ot_end = s.ot_end
          , p.ot_hours = s.ot_hours
          , p.ot_is_eating = s.ot_is_eating
          , p.ot_pending_hours = ISNULL(s.ot_pending_hours, 0)
          , p.ot_normal_hours = ISNULL(s.ot_normal_hours, 0)
          , p.ot_weekend_hours = ISNULL(s.ot_weekend_hours, 0)
          , p.ot_holiday_hours = ISNULL(s.ot_holiday_hours, 0)
          , p.leave_type = s.leave_type
          , p.leave_hours = s.leave_hours
          , p.ot_bill_id = s.ot_bill_id
          , p.sign_begin = s.sign_begin_date
          , p.sign_end = s.sign_end_date
          , p.is_swipe = ISNULL(s.is_swipe, 0)
          , p.leave_bill_id = s.leave_bill_id
          , p.ot_bill_status = s.ot_bill_status
          , p.swipe_begin_repair = s.swipe_begin_repair
          , p.swipe_end_repair = s.swipe_end_repair
          , p.op_flag =
            CASE
              WHEN @flag <> 0 THEN
                @flag
              ELSE
                p.op_flag
            END
          , p.should_hours_calc = ISNULL(s.should_hours_calc, 0)
          , p.actual_hours_calc = ISNULL(s.actual_hours_calc, 0)
          , p.ot_allowance = ISNULL(s.ot_allowance, 0)
--          , p.card_year = YEAR(@date)
--          , p.card_month = MONTH(@date)
          , p.shift_allowance_zb1 = ISNULL(s.shift_allowance_zb1, 0)
          , p.shift_allowance_zb2 = ISNULL(s.shift_allowance_zb2, 0)
          , p.shift_allowance_yb = ISNULL(s.shift_allowance_yb, 0)
          , p.work_calendar = ISNULL(s.work_calendar, 0)
          , p.work_status = ISNULL(s.work_status, 0)
          , p.swipe_first = ISNULL(s.swipe_first, 0)
          , p.swipe_last = ISNULL(s.swipe_last, 0)
          , p.settle_year = s.settle_year
          , p.settle_month = s.settle_month
          , p.late_hour = ISNULL(s.late_hour, 0)
          , p.early_hour = ISNULL(s.early_hour, 0)
          , p.off_duty_hour = ISNULL(s.off_duty_hour, 0)
          , p.leave_hours_total = ISNULL(s.leave_hours_total, 0)
    WHEN NOT MATCHED BY TARGET
      THEN INSERT(user_serial, user_no, user_name, dept_id, dept_name, card_date, card_week, shift_id, shift_name, shift_begin, shift_end, 
              swipe_begin, swipe_end, should_hours, actual_hours, total_hours, status, pending_hours, ot_bill_id, ot_type_id, ot_type_name, ot_begin, ot_end, ot_hours, 
              ot_is_eating, ot_pending_hours, leave_type, leave_hours, leave_hours_total, sign_begin, sign_end, is_swipe, 
              leave_bill_id, ot_bill_status, swipe_begin_repair, swipe_end_repair, op_flag,
              ot_normal_hours, ot_weekend_hours, ot_holiday_hours, ot_allowance, should_hours_calc, actual_hours_calc,
              shift_allowance_zb1, shift_allowance_zb2, shift_allowance_yb, card_year, card_month, work_calendar, work_status,
              swipe_first, swipe_last, settle_year, settle_month, late_hour, early_hour, off_duty_hour)
          VALUES(user_serial, user_no, user_name, dept_id, dept_name, card_date, card_week, shift_id, shift_name, shift_begin, shift_end, 
              swipe_begin, swipe_end, should_hours, actual_hours, total_hours, status, pending_hours, ot_bill_id, ot_type_id, ot_type_name, 
              ot_begin, ot_end, ot_hours, ot_is_eating, ot_pending_hours, leave_type, leave_hours, ISNULL(leave_hours_total, 0), sign_begin_date, sign_end_date, 
              ISNULL(is_swipe, 0), leave_bill_id, ot_bill_status, swipe_begin_repair, swipe_end_repair, @flag,
              ISNULL(ot_normal_hours, 0), ISNULL(ot_weekend_hours, 0), ISNULL(ot_holiday_hours, 0), ISNULL(ot_allowance, 0), ISNULL(should_hours_calc, 0), ISNULL(s.actual_hours_calc, 0),
              ISNULL(s.shift_allowance_zb1, 0), ISNULL(s.shift_allowance_zb2, 0), ISNULL(s.shift_allowance_yb, 0), YEAR(@date), MONTH(@date), 
              ISNULL(s.work_calendar, 0), ISNULL(s.work_status, 0), ISNULL(s.swipe_first, 0), ISNULL(s.swipe_last, 0),
              settle_year, settle_month, ISNULL(s.late_hour, 0), ISNULL(s.early_hour, 0), ISNULL(s.off_duty_hour, 0))
    ;

--    -- 更新考勤打卡记录表数据
--    UPDATE kj
--    SET whether_validity = cs.is_valid--, valid_date = CASE WHEN cs.is_valid IN (1, 3) THEN @date ELSE valid_date END
--    FROM #cardSave cs
--    INNER JOIN kt_jl kj ON cs.bh = kj.bh --AND kj.whether_validity < 2

    MERGE
    INTO bd_kq_overtime_actual_swipe p
    USING #overtime s ON p.order_detail_id = s.ot_detail_id
    WHEN MATCHED
      THEN UPDATE SET
            p.ot_swipe_begin = s.ot_time_swipe_begin
          , p.ot_swipe_end = s.ot_time_swipe_end
    WHEN NOT MATCHED BY TARGET
      THEN INSERT(order_detail_id, ot_swipe_begin, ot_swipe_end)
          VALUES(ot_detail_id, ot_time_swipe_begin, ot_time_swipe_end)
    ;

    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
    END;
    RAISERROR (@errStr, 16, 1);
  END CATCH;


  DROP TABLE #timecard
  DROP TABLE #cardData
  DROP TABLE #cardSave
  DROP TABLE #leave
  DROP TABLE #overtime
  DROP TABLE #users
  DROP TABLE #schedule  
  

  SET NOCOUNT OFF;
END